package com.xpgk.mvc.statistic.monthNursing;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.LogKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.render.Render;
import com.jfinal.render.RenderException;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class MonthNursingStatisticXLS extends  Render{

	private static final String CONTENT_TYPE = "application/msexcel;charset=UTF-8";

	private final List<Record> data;
	private final String searchCriteria;
	private final String fileName;

	public MonthNursingStatisticXLS(List<Record> data,String searchCriteria, String fileName) {
		this.data = data;
		this.searchCriteria = searchCriteria;
		this.fileName = fileName;
	}

	@Override
	public void render() {
		this.response.reset();
		try {
			this.response.setHeader("Content-disposition",
					"attachment; filename*=UTF-8''" + URLEncoder.encode(this.fileName, "UTF-8") + ".xls");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		this.response.setContentType(CONTENT_TYPE);

		OutputStream os = null;
		try {
			os = this.response.getOutputStream();
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet(this.fileName, 0);
			
			//主表头
			List<String> main_header = new ArrayList<>();
			main_header.add(fileName);
			for(int mhi = 0; mhi < 52; mhi++){
				main_header.add("");
			}
			
			//搜索表头
			List<String> search_header = new ArrayList<>();
			search_header.add("搜索条件：");
			search_header.add(searchCriteria);
			for(int mhi = 0; mhi < 51; mhi++){
				search_header.add("");
			}
			
			//注释表头
			List<String> ann_header = new ArrayList<>();
			ann_header.add("减重效果：");
			ann_header.add("30%以下为红色");
			ann_header.add("30%~40%为粉色");
			ann_header.add("40%~50%为紫色");
			ann_header.add("50%~60%为浅蓝色");
			ann_header.add("60%~70%为深蓝色");
			ann_header.add("70%~80%为浅绿色");
			ann_header.add("80%以上为绿色");
			for(int a=0;a<45;a++)
			{
				ann_header.add("");
			}
			//第一行表头
			List<String> one_header = new ArrayList<>();
			one_header.add("机构名称");
			one_header.add("姓名");
			one_header.add("手机");
			one_header.add("会员编号");
			one_header.add("进店日期");
			one_header.add("收取疗程数");
			one_header.add("最初体重（斤）");
			one_header.add("标准体重（斤）");
			one_header.add("应减斤数（斤）");
			for(int i = 1; i < 13; i++){
				one_header.add(i+"月");
				one_header.add(i+"月");
				one_header.add(i+"月");
			}
			one_header.add("最后到店时间");
			one_header.add("最后体重");
			one_header.add("减重次数");
			one_header.add("已减斤数");
			one_header.add("未减斤数");
			one_header.add("月均减重斤数");
			one_header.add("减重效果");
			one_header.add("是否达到标体");
			
			//第二行表头
			List<String> two_header = new ArrayList<>();
			two_header.add("");//占位
			two_header.add("");
			two_header.add("");
			two_header.add("");
			two_header.add("");//占位
			two_header.add("");
			two_header.add("");
			two_header.add("");
			two_header.add("");
			for(int j = 1; j < 13;j++){
				two_header.add("到店次数");
				two_header.add("最后体重");
				two_header.add("已减斤数");
			}
			two_header.add("");//占位
			two_header.add("");
			two_header.add("");
			two_header.add("");
			two_header.add("");//占位
			two_header.add("");
			two_header.add("");
			two_header.add("");
			//列名
			List<String> columns = new ArrayList<>();
			columns.add("organname");
			columns.add("cusname");
			columns.add("cusphone");
			columns.add("cushybh");
			columns.add("intostoredate");
			columns.add("weightoflastmonth");
			columns.add("initialweight");
			columns.add("stantardweight");
			columns.add("weightloss");
			for(int n = 1; n < 13; n++){
				columns.add("month_"+n+"_tostore");
				columns.add("month_"+n+"_lastweight");
				columns.add("month_"+n+"_yidel");
			}
			columns.add("lasttostore");
			columns.add("lastweight");
			columns.add("delweightnum");
			columns.add("haveDelWeight");
			columns.add("noDelWeight");
			columns.add("monthAverage");
			columns.add("jzxg");
			columns.add("isdb");

			int row = 0;//行数

			sheet.setColumnView(0, 17); // 设置列的宽度
			sheet.setColumnView(1, 17); 
			sheet.setColumnView(2, 17);
			sheet.setColumnView(3, 17);
			sheet.setColumnView(4, 17);// 设置列的宽度
			sheet.setColumnView(5, 17); // 设置列的宽度
			sheet.setColumnView(6, 17);
			sheet.setColumnView(7, 17);
			sheet.setColumnView(8, 17);
			sheet.setRowView(0, 600); // 设置行的高度
			sheet.setRowView(1, 600); // 设置行的高度
			sheet.setRowView(2, 600); // 设置行的高度
			sheet.setRowView(3, 600); // 设置行的高度

			//合并单元格
			sheet.mergeCells(0, 0, 52, 0);// 列，行，列，行
			sheet.mergeCells(1, 1, 52, 1);// 列，行，列，行
			sheet.mergeCells(0, 3, 0, 4);// 列，行，列，行
			sheet.mergeCells(1, 3, 1, 4);
			sheet.mergeCells(2, 3, 2, 4);
			sheet.mergeCells(3, 3, 3, 4);
			sheet.mergeCells(4, 3, 4, 4);
			sheet.mergeCells(5, 3, 5, 4);
			sheet.mergeCells(6, 3, 6, 4);
			sheet.mergeCells(7, 3, 7, 4);
			sheet.mergeCells(8, 3, 8, 4);
			
			int index = 9;
			for(int i =0; i < 12; i ++){
				int begin = 9 + i*3;
				int end = 11 + i*3;
				sheet.mergeCells(begin, 3, end, 3);
				index = end + 1;
			}
			sheet.mergeCells(index, 3, index, 4);
			sheet.mergeCells(index + 1, 3, index + 1, 4);
			sheet.mergeCells(index + 2, 3, index + 2, 4);
			sheet.mergeCells(index + 3, 3, index + 3, 4);
			sheet.mergeCells(index + 4, 3, index + 4, 4);
			sheet.mergeCells(index + 5, 3, index + 5, 4);
			sheet.mergeCells(index + 6, 3, index + 6, 4);
			sheet.mergeCells(index + 7, 3, index + 7, 4);
			
			//主表头样式
			WritableFont wf = new WritableFont(WritableFont.TIMES, 18,  WritableFont.BOLD, true);
			WritableCellFormat wcfF = new WritableCellFormat(wf);
			wcfF.setBackground(Colour.GRAY_25);// 背景色
			wcfF.setAlignment(Alignment.CENTRE);// 水平对齐
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			wcfF.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//搜索表头样式
			WritableCellFormat search_format = new WritableCellFormat();
			search_format.setBackground(Colour.GRAY_25);// 背景色
			search_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			search_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//表头样式
			WritableCellFormat header_format = new WritableCellFormat();
			header_format.setBackground(Colour.GRAY_25);// 背景色
			header_format.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			header_format.setWrap(true);// 是否换行
			WritableFont red_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
			WritableCellFormat header_format_red = new WritableCellFormat(red_font);
			header_format_red.setBackground(Colour.GRAY_25);// 背景色
			header_format_red.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_red.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_red.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont pink_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.PINK);
			WritableCellFormat header_format_pink = new WritableCellFormat(pink_font);
			header_format_pink.setBackground(Colour.GRAY_25);// 背景色
			header_format_pink.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_pink.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_pink.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont violet_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.VIOLET);
			WritableCellFormat header_format_violet = new WritableCellFormat(violet_font);
			header_format_violet.setBackground(Colour.GRAY_25);// 背景色
			header_format_violet.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_violet.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_violet.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont skyblue_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.SKY_BLUE);
			WritableCellFormat header_format_skyblue = new WritableCellFormat(skyblue_font);
			header_format_skyblue.setBackground(Colour.GRAY_25);// 背景色
			header_format_skyblue.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_skyblue.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_skyblue.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont blue_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
			WritableCellFormat header_format_blue = new WritableCellFormat(blue_font);
			header_format_blue.setBackground(Colour.GRAY_25);// 背景色
			header_format_blue.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_blue.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_blue.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont lightgreen_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.LIGHT_GREEN);
			WritableCellFormat header_format_lightgreen = new WritableCellFormat(lightgreen_font);
			header_format_lightgreen.setBackground(Colour.GRAY_25);// 背景色
			header_format_lightgreen.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_lightgreen.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_lightgreen.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont green_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.GREEN);
			WritableCellFormat header_format_green = new WritableCellFormat(green_font);
			header_format_green.setBackground(Colour.GRAY_25);// 背景色
			header_format_green.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_green.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format_green.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//生成主表头
			for (int mi = 0; mi < main_header.size(); mi++) {
				String hea = main_header.get(mi);
				sheet.addCell(new Label(mi, row, hea, wcfF));
			}
			row++;
			//生成搜索表头
			for (int si = 0; si < search_header.size(); si++) {
				String hea = search_header.get(si);
				if(si==0)
				{
					sheet.addCell(new Label(si, row, hea, header_format));
				}else
				{
					sheet.addCell(new Label(si, row, hea, search_format));
				}
				
			}
			row++;
			//生成注释表头n
			for (int an = 0; an < ann_header.size(); an++) {
				String header = ann_header.get(an);
				if(an==1)
				{
					sheet.addCell(new Label(an, row, header, header_format_red));
				}
				else if(an==2)
				{
					sheet.addCell(new Label(an, row, header, header_format_pink));
				}
				else if(an==3)
				{
					sheet.addCell(new Label(an, row, header, header_format_violet));
				}
				else if(an==4)
				{
					sheet.addCell(new Label(an, row, header, header_format_skyblue));
				}
				else if(an==5)
				{
					sheet.addCell(new Label(an, row, header, header_format_blue));
				}
				else if(an==6)
				{
					sheet.addCell(new Label(an, row, header, header_format_lightgreen));
				}
				else if(an==7)
				{
					sheet.addCell(new Label(an, row, header, header_format_green));
				}
				else
				{
					sheet.addCell(new Label(an, row, header, header_format));
				}
				
			}
			row++;
			
			
			//生成第一行表头
			for (int i = 0; i < one_header.size(); i++) {
				String header = one_header.get(i);
				sheet.addCell(new Label(i, row, header, header_format));
			}

			row++;
			
			//生成第二行表头
			for (int m = 0; m < two_header.size(); m++) {
				String header = two_header.get(m);
				sheet.addCell(new Label(m, row, header, header_format));
			}

			row++;

			//表格样式
			WritableCellFormat row_format = new WritableCellFormat();
			row_format.setAlignment(Alignment.CENTRE);// 水平对齐
			row_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			row_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//表格样式(红)
			WritableCellFormat red_format = new WritableCellFormat();
			red_format.setAlignment(Alignment.CENTRE);// 水平对齐
			red_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			red_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			red_format.setBackground(Colour.RED);
			//表格样式(粉)
			WritableCellFormat pink_format = new WritableCellFormat();
			pink_format.setAlignment(Alignment.CENTRE);// 水平对齐
			pink_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			pink_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			pink_format.setBackground(Colour.PINK);
			//表格样式(紫)
			WritableCellFormat violet_format = new WritableCellFormat();
			violet_format.setAlignment(Alignment.CENTRE);// 水平对齐
			violet_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			violet_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			violet_format.setBackground(Colour.VIOLET);
			//表格样式(浅蓝)
			WritableCellFormat skyblue_format = new WritableCellFormat();
			skyblue_format.setAlignment(Alignment.CENTRE);// 水平对齐
			skyblue_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			skyblue_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			skyblue_format.setBackground(Colour.SKY_BLUE);
			//表格样式(蓝)
			WritableCellFormat blue_format = new WritableCellFormat();
			blue_format.setAlignment(Alignment.CENTRE);// 水平对齐
			blue_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			blue_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			blue_format.setBackground(Colour.BLUE);
			//表格样式(浅绿)
			WritableCellFormat lightgreen_format = new WritableCellFormat();
			lightgreen_format.setAlignment(Alignment.CENTRE);// 水平对齐
			lightgreen_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			lightgreen_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			lightgreen_format.setBackground(Colour.LIGHT_GREEN);
			//表格样式(绿)
			WritableCellFormat green_format = new WritableCellFormat();
			green_format.setAlignment(Alignment.CENTRE);// 水平对齐
			green_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			green_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			green_format.setBackground(Colour.GREEN);
			//生成表
			for (int j = 0; j < data.size(); j++) {
				Record record = (Record) data.get(j);

				String[] values = getValues(columns, record);
				String a=values[48];
				String b=values[8];
				float jzxg=Float.parseFloat(a)/Float.parseFloat(b)*100;
				for (int i = 0; i < values.length; i++)
				{	
					if(values[i]!=null)
					{
						if(i>5 && i<45)
						{
							sheet.addCell(new jxl.write.Number(i, row, Double.parseDouble(values[i]), row_format));
						}else if(i>45 && i<51)
						{
							sheet.addCell(new jxl.write.Number(i, row, Double.parseDouble(values[i]), row_format));
						}
						else if(i==51)
						{
							if(jzxg<30){sheet.addCell(new Label(i, row, values[i], red_format));}
							else if(30<jzxg && jzxg<40){sheet.addCell(new Label(i, row, values[i], pink_format));}
							else if(40<jzxg && jzxg<50){sheet.addCell(new Label(i, row, values[i], violet_format));}
							else if(50<jzxg && jzxg<60){sheet.addCell(new Label(i, row, values[i], skyblue_format));}
							else if(60<jzxg && jzxg<70){sheet.addCell(new Label(i, row, values[i], blue_format));}
							else if(70<jzxg && jzxg<80){sheet.addCell(new Label(i, row, values[i], lightgreen_format));}
							else {sheet.addCell(new Label(i, row, values[i], green_format));}
						}
						else
						{
							sheet.addCell(new Label(i, row, values[i], row_format));
						}
					}else
					{
						sheet.addCell(new Label(i, row, values[i], row_format));
					}
				}
				row++;
			}

			workbook.write();
			workbook.close();
		} catch (IOException e) {
			throw new RenderException(e);
		} catch (WriteException e) {
			e.printStackTrace();
		} finally {
			try {
				if (os != null) {
					os.flush();
					os.close();
				}
			} catch (IOException e) {
				LogKit.error(e.getMessage(), e);
			}
		}
	}

	private static String[] getValues(List<String> columns, Record record) {
		String[] values = new String[columns.size()];
		int i = -1;
		for (String col : columns) {
			i++;
			Object value = record.get(col);
			if (value == null) {
				continue;
			}
			values[i] = value.toString();
		}
		return values;
	}


}
